package fina2.dcs.upload;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.Serializable;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Properties;
import java.util.ResourceBundle;
import java.util.Set;
import java.util.TreeMap;
import java.util.TreeSet;

import javax.annotation.PostConstruct;
import javax.annotation.PreDestroy;
import javax.annotation.Resource;
import javax.ejb.EJB;
import javax.ejb.PostActivate;
import javax.ejb.PrePassivate;
import javax.ejb.Remove;
import javax.ejb.SessionContext;
import javax.ejb.Stateless;
import javax.ejb.TransactionManagement;
import javax.ejb.TransactionManagementType;
import javax.faces.component.UIViewRoot;
import javax.faces.context.FacesContext;
import javax.interceptor.Interceptors;
import javax.persistence.EntityManager;
import javax.persistence.NoResultException;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.TemporalType;
import javax.transaction.UserTransaction;
import javax.xml.bind.JAXBContext;
import javax.xml.bind.JAXBException;
import javax.xml.bind.Marshaller;

import org.apache.log4j.Logger;
import org.jboss.annotation.ejb.LocalBinding;
import org.jboss.seam.ScopeType;
import org.jboss.seam.annotations.AutoCreate;
import org.jboss.seam.annotations.Name;
import org.jboss.seam.annotations.Scope;
import org.jboss.seam.annotations.Synchronized;

import fina2.dcs.common.resources.MessageBundleReader;
import fina2.dcs.common.returns.jaxb.BODYTYPE;
import fina2.dcs.common.returns.jaxb.HEADERTYPE;
import fina2.dcs.common.returns.jaxb.ITEMTYPE;
import fina2.dcs.common.returns.jaxb.RETURN;
import fina2.dcs.common.security.UserType;
import fina2.dcs.common.security.UserUtil;
import fina2.dcs.common.upload.UploadedFileInfo;
import fina2.dcs.common.util.FileUtil;
import fina2.dcs.common.xls.DCSException;
import fina2.dcs.common.xls.DCSExceptionConstants;
import fina2.dcs.common.xls.MatrixException;
import fina2.dcs.common.xls.MatrixExceptionConstants;
import fina2.dcs.common.xls.MatrixOptionsRowInfo;
import fina2.dcs.common.xls.MatrixUtil;
import fina2.dcs.common.xls.XLSReader;
import fina2.dcs.common.xls.XLSValidator;
import fina2.dcs.common.xls.XlsFileProperties;
import fina2.dcs.common.xls.XlsNameParser;
import fina2.dcs.fis.BankLocal;
import fina2.dcs.i18n.Language;
import fina2.dcs.i18n.LanguageLocal;
import fina2.dcs.returns.ImportReturn;
import fina2.dcs.returns.ImportReturnLocal;
import fina2.dcs.returns.ImportReturnStatus;
import fina2.dcs.security.User;
import fina2.dcs.security.UserLocal;
import fina2.dcs.system.SysPropertyLocal;
import fina2.dcs.system.SysUserBankLocal;

@Stateless
@Scope(ScopeType.SESSION)
@Name("uploadFileSession")
@SuppressWarnings({ "serial", "unchecked" })
@LocalBinding(jndiBinding = "UploadedFileSession/local")
@AutoCreate
@Synchronized(timeout = 1000000000)
@TransactionManagement(TransactionManagementType.BEAN)
public class UploadedFileSession implements UploadedFileLocal, Serializable {

	@PersistenceContext
	// (type = PersistenceContextType.EXTENDED)
	private EntityManager em;

	@Resource
	private SessionContext sc;

	// @Logger
	private Logger log = Logger.getLogger(getClass());

	private long fileId;

	private long fileToRemove = -1;

	private List<UploadedFile> uploadedFiles = new ArrayList<UploadedFile>();

	private List<ImportReturn> xmls = new ArrayList<ImportReturn>();

	private String bankCode = null;
	private Date periodStart = null;
	private Date periodEnd = null;

	private List<Long> fileIds = null;
	@EJB
	private LanguageLocal langLocal;
	@EJB
	private UserLocal userLocal;
	@EJB
	private BankLocal bankLocal;

	@EJB
	private SysUserBankLocal sysUserBankLocal;
	@EJB
	private SysPropertyLocal sysPropLocal;

	@EJB
	private ImportReturnLocal irLocal;

	// @Interceptors(UploadedFileInterceptor.class)
	public void removeFile(ArrayList<Long> ids) {
		try {
			log.info("Trying to remove");

			if (!ids.isEmpty()) {
				UserTransaction ut = sc.getUserTransaction();
				ut.begin();
				em.createQuery("DELETE FROM " + UploadedFile.class.getName() + " u WHERE u.id IN (:ids)").setParameter("ids", ids).executeUpdate();
				ut.commit();
			}
			log.info("Removed Successfully");

		} catch (Exception ex) {
			log.error(ex.getMessage(), ex);
		}
	}

	public long getFileToRemove() {
		return fileToRemove;
	}

	public void setFileToRemove(long fileToRemove) {
		this.fileToRemove = fileToRemove;
	}

	private static Set<String> getPeriods(Date start, Date end) {
		Set<String> strs = new TreeSet<String>();
		SimpleDateFormat f = new SimpleDateFormat("MMyyyy");
		for (long i = start.getTime(); i <= end.getTime(); i += 86400000) {
			strs.add(f.format(new Date(i)));
		}
		return strs;
	}

	private TreeMap<String, Integer> loadStatuses() {
		ResourceBundle bundle = null;
		UIViewRoot vr = FacesContext.getCurrentInstance().getViewRoot();
		Locale l = vr.getLocale();
		ClassLoader cl = Thread.currentThread().getContextClassLoader();
		bundle = ResourceBundle.getBundle("messages", l, cl);
		TreeMap<String, Integer> statuses = new TreeMap<String, Integer>();
		statuses.put(bundle.getString("fina2.web.converted"), UploadFileStatus.CONVERTED.ordinal());
		statuses.put(bundle.getString("fina2.web.rejected"), UploadFileStatus.REJECTED.ordinal());
		statuses.put(bundle.getString("fina2.web.uploaded"), UploadFileStatus.UPLOADED.ordinal());
		statuses.put(bundle.getString("fina2.web.uploaded.converted"), UploadFileStatus.UPLOADED_CONVERTED.ordinal());
		statuses.put(bundle.getString("fina2.web.uploaded.error.in.processing"), UploadFileStatus.UPLOADED_ERROR_PROCESSING.ordinal());
		statuses.put(bundle.getString("fina2.web.uploaded.not.converted"), UploadFileStatus.UPLOADED_NOT_CONVERTED.ordinal());
		statuses.put(bundle.getString("fina2.web.uploaded.not.validated"), UploadFileStatus.UPLOADED_NOT_VALIDATED.ordinal());
		statuses.put(bundle.getString("fina2.web.uploaded.processed"), UploadFileStatus.UPLOADED_PROCESSED.ordinal());
		statuses.put(bundle.getString("fina2.web.uploaded.validated"), UploadFileStatus.UPLOADED_VALIDATED.ordinal());
		return statuses;
	}

	// @TransactionAttribute(TransactionAttributeType.REQUIRES_NEW)
	public List<UploadedFile> loadFilesToDownload(ArrayList<Long> ids) throws Exception {
		log.info("Getting files to download");
		List<UploadedFile> files = new ArrayList<UploadedFile>();
		List<Object[]> rows = null;
		try {
			Query q = em.createQuery("SELECT f.id,f.fileName,f.uploadedFile FROM " + UploadedFile.class.getName() + " f WHERE f.id IN(:ids)");
			q.setParameter("ids", ids);
			rows = q.getResultList();

			if (rows != null) {
				for (int i = 0; i < rows.size(); i++) {
					UploadedFile file = new UploadedFile();
					String name = rows.get(i)[1] + "";
					file.setId(Long.parseLong(rows.get(i)[0].toString()));
					file.setFileName(name);
					file.setUploadedFile((byte[]) rows.get(i)[2]);
					files.add(file);
				}
				changeNames(files);
				rows.clear();
			}

		} catch (Exception ex) {
			log.error(ex.getMessage(), ex);
			throw ex;
		}
		log.info("Got files to download");
		return files;
	}

	private void changeNames(List<UploadedFile> files) {
		Map<String, Integer> freqMap = new HashMap<String, Integer>();

		for (int j = 0; j < files.size(); j++) {
			String currName = files.get(j).getFileName();
			int freq = 0;
			for (int i = 0; i < files.size(); i++) {
				if (files.get(i).getFileName().equals(currName))
					freq++;
			}
			freqMap.put(currName, freq);
		}

		Iterator<String> freqMapIter = freqMap.keySet().iterator();
		while (freqMapIter.hasNext()) {
			String name = freqMapIter.next();
			int freq = freqMap.get(name);
			if (freq > 1) {
				for (int i = 0; i < files.size(); i++) {
					if (files.get(i).getFileName().equals(name)) {
						String currName = files.get(i).getFileName();
						int extentionIndex = currName.lastIndexOf('.');
						if (extentionIndex != -1) {
							String extentions = currName.substring(extentionIndex);
							files.get(i).setFileName(currName.replace(extentions, "(" + freq + ")" + extentions));
						} else {
							files.get(i).setFileName(currName + "(" + freq + ")");
						}
						freq--;
					}
					if (freq <= 1)
						break;
				}
			}
		}

	}

	// @Interceptors(UploadedFileInterceptor.class)
	public List<UploadedFile> loadUploadedFiles(UploadedFile uf) {
		String sql = "";
		if (uf == null)
			return new ArrayList<UploadedFile>();
		log.info("Loading Uploaded files for " + uf);
		try {
			ResourceBundle bundle = null;
			FacesContext fc = FacesContext.getCurrentInstance();
			if (fc != null) {
				UIViewRoot vr = fc.getViewRoot();
				Locale l = vr.getLocale();
				ClassLoader cl = Thread.currentThread().getContextClassLoader();
				bundle = ResourceBundle.getBundle("messages", l, cl);
			} else {
				bundle = MessageBundleReader.loadMessageBundle();
			}
			List<Object[]> upList = new ArrayList<Object[]>();

			uploadedFiles.clear();
			List<String> userFis = sysUserBankLocal.getAllUsersBankCodes(uf.getUserId());

			String all = bundle.getString("fina2.all").trim().toLowerCase();
			String periodCriteria = bundle.getString("fina2.web.by.period");

			String username = uf.getUsername();
			String fi = uf.getBankCode();
			String myFis = bundle.getString("fina2.dcs.user.myfis");
			String status = uf.getStatus();
			Date fromDate = uf.getFromDate();
			Date toDate = uf.getToDate();
			String selectedCriteria = uf.getPeriodCriteria();

			String usernameSQL = " f.userId=:userId ";
			String fiSQL = " AND RTRIM(f.bankCode)=:bankCode ";
			String statusSQL = " AND RTRIM(f.status)=:status ";
			String fromDateUploadedSQL = " AND f.uploadedTime>=:fromDate ";
			String toDateUploadedSQL = " AND f.uploadedTime<=:toDate ";
			String periodSQL = " AND f.fileName ";

			if (selectedCriteria.equals(periodCriteria)) {
				if (fromDate == null) {
					Calendar c = Calendar.getInstance();
					c.set(Calendar.DAY_OF_MONTH, 1);
					c.set(Calendar.MONTH, Calendar.JANUARY);
					c.set(Calendar.YEAR, 1970);
					fromDate = c.getTime();
				}
				if (toDate == null) {
					toDate = new Date();
				}
				Set<String> periods = getPeriods(fromDate, toDate);
				Iterator<String> periodIter = periods.iterator();
				periodSQL += "IN (SELECT ff.fileName FROM " + UploadedFile.class.getName() + " ff WHERE ";
				while (periodIter.hasNext()) {
					String curr = periodIter.next();
					periodSQL += " ff.fileName LIKE '%" + curr + "%' OR";
				}

				StringBuffer sb = new StringBuffer(periodSQL);
				sb.replace(periodSQL.length() - 2, periodSQL.length(), " ");
				periodSQL = sb.toString();
				periodSQL += ")";
			}

			if (username.trim().toLowerCase().equals(all)) {
				usernameSQL = "";
				fiSQL = " RTRIM(f.bankCode)=:bankCode ";
			}
			if (fi.trim().toLowerCase().equals(all)) {
				fiSQL = "";
				if (usernameSQL.equals("")) {
					statusSQL = " RTRIM(f.status)=:status ";
				}
			}
			if (fi.trim().toLowerCase().equals(myFis.trim().toLowerCase())) {

				{
					if (usernameSQL.equals(""))
						fiSQL = "  RTRIM(f.bankCode) IN (";
					else
						fiSQL = " AND RTRIM(f.bankCode) IN (";
					String codes = "";
					if (userFis != null && userFis.size() != 0) {
						for (int i = 0; i < userFis.size(); i++) {
							if (i != userFis.size() - 1)
								codes += "'" + userFis.get(i).trim() + "',";
							else
								codes += "'" + userFis.get(i).trim() + "','UNKNOWN')";
						}
						fiSQL += codes;
					} else {
						fiSQL = "";
					}
				}
				// if (usernameSQL.equals("")) {
				// statusSQL = " RTRIM(f.status)=:status ";
				// }
			}
			if (status.trim().toLowerCase().equals(all)) {
				statusSQL = "";
				if (fiSQL.equals("") && usernameSQL.equals("")) {
					fromDateUploadedSQL = " f.uploadedTime>=:fromDate ";
				}
			}

			if (!selectedCriteria.equals(periodCriteria)) {

				if ((fromDate == null) || (fromDate.toString().trim().length() == 0)) {
					fromDateUploadedSQL = "";
					if (statusSQL.equals("") && fiSQL.equals("") && usernameSQL.equals(""))
						toDateUploadedSQL = " f.uploadedTime<=:toDate ";
				}
				if ((toDate == null) || (toDate.toString().trim().length() == 0)) {
					toDateUploadedSQL = "";
				}
			}

			if (usernameSQL.equals("") && fiSQL.equals("") && statusSQL.equals("") && fromDateUploadedSQL.equals("") && toDateUploadedSQL.equals("")) {
				sql = "SELECT  f.id,f.bankCode,f.userId,f.fileName,f.status,f.uploadedTime FROM " + UploadedFile.class.getName() + " f ";
			} else {
				if (!selectedCriteria.equals(periodCriteria)) {
					sql = "SELECT  f.id,f.bankCode,f.userId,f.fileName,f.status,f.uploadedTime FROM " + UploadedFile.class.getName() + " f " + " WHERE " + usernameSQL + " " + fiSQL + " " + statusSQL + " " + fromDateUploadedSQL + " " + toDateUploadedSQL;
				} else {
					if (usernameSQL.equals("") && statusSQL.equals("") && fiSQL.equals(""))
						periodSQL = periodSQL.replace("AND", "  ");
					sql = "SELECT  f.id,f.bankCode,f.userId,f.fileName,f.status,f.uploadedTime FROM " + UploadedFile.class.getName() + " f " + " WHERE " + usernameSQL + " " + fiSQL + " " + statusSQL + " " + periodSQL + "";
				}
			}

			Query q = em.createQuery(sql);

			if (!username.trim().toLowerCase().equals(all)) {
				q.setParameter("userId", userLocal.getUserId(username));
			}
			if ((!fi.trim().toLowerCase().equals(all)) && (!fi.trim().toLowerCase().equals(myFis.trim().toLowerCase()))) {
				q.setParameter("bankCode", fi.trim());
			}
			// if (fi.trim().toLowerCase().equals(myFis.trim().toLowerCase())) {
			// q.setParameter("userFis", userFis);
			// }
			if (!status.trim().toLowerCase().equals(all)) {
				TreeMap<String, Integer> statuses = loadStatuses();
				if (statuses.containsKey(status)) {
					Integer i = Integer.parseInt(statuses.get(status).toString());
					q.setParameter("status", i.toString().trim());
				}
			}
			if (!selectedCriteria.equals(periodCriteria)) {
				if ((fromDate != null) && (fromDate.toString().trim().length() != 0)) {
					q.setParameter("fromDate", fromDate, TemporalType.TIMESTAMP);
				}
				if ((toDate != null) && (toDate.toString().trim().length() != 0)) {
					Calendar c = Calendar.getInstance();
					c.setTime(toDate);
					c.set(Calendar.DAY_OF_MONTH, c.get(Calendar.DAY_OF_MONTH) + 1);
					Date newDate = c.getTime();
					q.setParameter("toDate", newDate, TemporalType.TIMESTAMP);
				}
			}

			if (q != null) {
				if (fileIds == null) {
					fileIds = new ArrayList<Long>();
				} else {
					fileIds.clear();
				}

				upList = q.getResultList();
				// uploadedFiles.clear();
				for (int i = 0; i < upList.size(); i++) {
					UploadedFile f = new UploadedFile();

					f.setId(new Integer(upList.get(i)[0].toString()));
					fileIds.add(f.getId());
					if (upList.get(i)[1] != null)
						f.setBankCode(upList.get(i)[1].toString());
					else
						f.setBankCode("UNKNOWN");
					f.setUserId(new BigDecimal(upList.get(i)[2].toString()));
					f.setUsername(userLocal.findById(f.getUserId()));
					f.setFileName(upList.get(i)[3].toString());
					String curStatus = upList.get(i)[4].toString().trim();

					if (curStatus.equals(UploadFileStatus.CONVERTED.toString().trim())) {
						f.setStatus(UploadFileStatus.CONVERTED.getCode());
					} else if (curStatus.equals(new Integer(UploadFileStatus.REJECTED.ordinal()).toString())) {
						f.setStatus(UploadFileStatus.REJECTED.getCode());
					} else if (curStatus.equals(new Integer(UploadFileStatus.UPLOADED.ordinal()).toString())) {
						f.setStatus(UploadFileStatus.UPLOADED.getCode());
					} else if (curStatus.equals(new Integer(UploadFileStatus.UPLOADED_CONVERTED.ordinal()).toString())) {
						f.setStatus(UploadFileStatus.UPLOADED_CONVERTED.getCode());
					} else if (curStatus.equals(new Integer(UploadFileStatus.UPLOADED_ERROR_PROCESSING.ordinal()).toString())) {
						f.setStatus(UploadFileStatus.UPLOADED_ERROR_PROCESSING.getCode());
					} else if (curStatus.equals(new Integer(UploadFileStatus.UPLOADED_NOT_CONVERTED.ordinal()).toString())) {
						f.setStatus(UploadFileStatus.UPLOADED_NOT_CONVERTED.getCode());
					} else if (curStatus.equals(new Integer(UploadFileStatus.UPLOADED_NOT_VALIDATED.ordinal()).toString())) {
						f.setStatus(UploadFileStatus.UPLOADED_NOT_VALIDATED.getCode());
					} else if (curStatus.equals(new Integer(UploadFileStatus.UPLOADED_PROCESSED.ordinal()).toString())) {
						f.setStatus(UploadFileStatus.UPLOADED_PROCESSED.getCode());
					} else if (curStatus.equals(new Integer(UploadFileStatus.UPLOADED_VALIDATED.ordinal()).toString())) {
						f.setStatus(UploadFileStatus.UPLOADED_VALIDATED.getCode());
					}
					f.setUploadedTime((Date) upList.get(i)[5]);

					uploadedFiles.add(f);
				}
			}
		} catch (Exception ex) {
			log.debug(sql);
			log.error(ex.getMessage(), ex);
		}
		log.info("Files Loaded");
		return uploadedFiles;
	}

	public List<Long> getFileIds() {
		return fileIds;
	}

	public List<UploadedFile> getUploadedFiles() {
		log.info("########Getting Uploaded File(s) ");
		return uploadedFiles;
	}

	public void setUploadedFiles(List<UploadedFile> uploadedFiles) {
		this.uploadedFiles = uploadedFiles;
	}

	public List<UploadedFileInfo> upload(String name, byte[] content, User user, String spDefaultPattern, String spMfbHisPattern, String spProtPass) {
		UploadedFile uf = new UploadedFile(UploadType.EMAIL_ROBOT);
		List<UploadedFileInfo> statusDesc = null;
		long id = -1;
		String stat = "";
		UserTransaction ut = sc.getUserTransaction();
		try {

			ut.begin();
			if (name.length() > 50)
				uf.setFileName(name.substring(0, 50));
			else
				uf.setFileName(name);
			uf.setUploadedTime(new Timestamp(new Date().getTime()));
			uf.setStatus(new Integer(UploadFileStatus.UPLOADED.ordinal()).toString());
			uf.setUsername(user.getLogin());
			uf.setUserId(user.getId());
			uf.setUploadedFile(content);
			em.persist(uf);
			ut.commit();
			id = uf.getId();

			ut.begin();
			validateXlsFileAndConvert(id, spProtPass, null, user.getId(), sysPropLocal.findPropValue("fina2.dcs.matrix.path"));

			ut.commit();

			ut.begin();
			UploadedFile uff = findByPrimaryKey(id);
			ut.commit();
			stat = uff.getStatus();
		} catch (Exception ex) {
			log.error(ex.getMessage(), ex);
		}

		int i = Integer.parseInt(stat);
		statusDesc = new ArrayList<UploadedFileInfo>();
		UploadedFileInfo fileInfo = new UploadedFileInfo();

		if (i == UploadFileStatus.UPLOADED_NOT_VALIDATED.ordinal()) {
			Object[] statusReasons = null;
			try {
				ut.begin();
				Query q = em.createQuery("SELECT u.nameValid,u.passwordInfo,u.excelVersionValid,u.protectionInfo,u.passwordInfo,u.hasUserBank,u.matrixValid,u.reason FROM " + UploadedFile.class.getName() + " u WHERE u.id=:id");
				q.setParameter("id", id);
				statusReasons = (Object[]) q.getSingleResult();
				ut.commit();
			} catch (Exception ex) {
				log.error(ex.getMessage(), ex);
			}
			List<String> messageCodes = new ArrayList<String>();

			boolean nameValid = (Boolean) statusReasons[0];
			Object pwdValid = statusReasons[1];
			boolean passwordValid = ((pwdValid == null) || (pwdValid.toString().equals("[]"))) ? true : false;
			boolean versionValid = (Boolean) statusReasons[2];
			Object protInfo = statusReasons[3];
			String protectionInfo = (protInfo != null) ? protInfo.toString() : null;
			String passwordInfo = (String) statusReasons[4];
			boolean hasUserBank = (Boolean) statusReasons[5];
			boolean matrixValid = (Boolean) statusReasons[6];
			String reason = (String) statusReasons[7];

			if (!versionValid) {
				messageCodes.add("fina2.dcs.wrong.file.type");
				fileInfo.setStatus(i);
				fileInfo.setMessageCodes(messageCodes);
				statusDesc.add(fileInfo);
				return statusDesc;
			} else {
				if (!passwordValid) {
					messageCodes.add("fina2.web.xls.protection.password.valid");
				}
			}

			if (!nameValid) {
				messageCodes.add("fina2.dcs.wrong.file.name");
				fileInfo.setStatus(i);
				fileInfo.setMessageCodes(messageCodes);
				statusDesc.add(fileInfo);
				return statusDesc;
			}

			if (!matrixValid) {
				if (reason != null && reason.equals(MatrixExceptionConstants.MATRIX_SHEET_CONFIRMATION_ERROR.getMessageCode())) {
					messageCodes.add(MatrixExceptionConstants.SHEET_PROTECTION_ERROR.getMessageCode());
					fileInfo.setStatus(i);
					fileInfo.setMessageCodes(messageCodes);
					if (protInfo != null)
						fileInfo.setAdditionalInfo(protInfo.toString());
					statusDesc.add(fileInfo);
					return statusDesc;
				} else {
					messageCodes.add(reason);
					fileInfo.setStatus(i);
					fileInfo.setMessageCodes(messageCodes);
					statusDesc.add(fileInfo);
					return statusDesc;
				}
			}

			if ((!hasUserBank) && (!messageCodes.contains("fina2.web.xls.name.valid"))) {
				messageCodes.add("fina2.web.user.bank");
			}
			// if (!matrixValid) {
			// // messageCodes.add("fina2.dcs.matrix.error");
			// messageCodes.add(reason);
			// }

			if ((protectionInfo != null) && (!"[]".equals(protectionInfo))) {
				messageCodes.add("fina2.dcs.not.proteced.sheets");
				// messageCodes.add(protectionInfo);
			}
			if ((passwordInfo != null) && (!"[]".equals(passwordInfo))) {
				messageCodes.add("fina2.dcs.not.correct.pwd.sheets");
				// messageCodes.add(passwordInfo);
			}

			fileInfo.setStatus(i);
			fileInfo.setMessageCodes(messageCodes);
			statusDesc.add(fileInfo);
		} else {
			fileInfo.setStatus(i);
			statusDesc.add(fileInfo);
		}
		return statusDesc;
	}

	public void upload(String name, byte[] content, User user, String spDefaultPattern, String spMfbHisPattern, String spProtPass, Properties p) {

		try {
			UserTransaction ut = sc.getUserTransaction();
			ut.begin();
			UploadedFile uf = new UploadedFile(UploadType.DCS);

			uf.setBankCode("UNKNOWN");
			if (name.length() > 50)
				uf.setFileName(name.substring(0, 50));
			else
				uf.setFileName(name);
			uf.setUploadedTime(new Timestamp(new Date().getTime()));
			uf.setStatus(new Integer(UploadFileStatus.UPLOADED.ordinal()).toString());
			uf.setUsername(user.getLogin());
			uf.setUserId(user.getId());
			uf.setUploadedFile(content);
			em.persist(uf);
			ut.commit();
			ut.begin();
			validateXlsFileAndConvert(uf.getId(), spProtPass, p, user.getId(), sysPropLocal.findPropValue("fina2.dcs.matrix.path"));
			ut.commit();
		} catch (Exception ex) {
			log.error(ex.getMessage(), ex);
		}
	}

	public boolean validateXlsFileAndConvert(long id, String spProtPass, Properties p, BigDecimal curUserId, String mainMatrixPath) {

		synchronized (UploadedFileSession.class) {
			XlsFileProperties xlsFileProps = new XlsFileProperties();
			boolean hasUserBank = false;

			UploadedFile curUploadedFile = null;

			// File temp = null;
			String tempName = null;
			boolean isXlsFile = false;
			String username = "";
			byte[] currFileContent = null;
			// UploadedFileLocal uploadFileLocal = null;
			try {

				if (p == null) {
					MatrixUtil mu = new MatrixUtil();
					p = mu.loadProperties();
				}

				curUploadedFile = findByPrimaryKey(id);
				if (curUploadedFile == null) {
					log.error("File id is invalid");
					throw new IllegalArgumentException();
				} else {
					tempName = curUploadedFile.getFileName();
					// temp = new File(curUploadedFile.getFileName());
					ByteArrayOutputStream bos = new ByteArrayOutputStream();
					currFileContent = curUploadedFile.getUploadedFile();
					bos.write(currFileContent);
					bos.flush();
					bos.close();
				}

				// tempName = temp.getName();

				if (!userLocal.hasPermission(curUserId, "fina2.returns.process")) {
					curUserId = userLocal.getUserId("sa");
				}

				username = userLocal.findById(curUserId);
				List<String> permissions = userLocal.getUserPermissions(curUserId);
				UserType userType = UserUtil.getUserType(username, permissions);
				List<String> userFiTypes = userLocal.loadUserFiTypes(curUserId);
				XLSValidator.loadXlsFileProps(tempName, userType, userFiTypes, xlsFileProps, p, mainMatrixPath);

				isXlsFile = XLSValidator.isXLSFile(currFileContent);
				xlsFileProps.setXlsFile(isXlsFile);

				XLSValidator.validateMatrix(tempName, currFileContent, userType, userFiTypes, xlsFileProps, p, mainMatrixPath);

				String bankCode = XlsNameParser.getBankCode(tempName, xlsFileProps.getXlsCorrectPattern());

				List<String> userBanks = (!username.equals("sa")) ? sysUserBankLocal.getAllUsersBankCodes(curUserId) : bankLocal.getAllBankCodes();
				hasUserBank = userBanks.contains(bankCode);
				if (hasUserBank) {
					curUploadedFile.setBankCode(bankCode);
				} else {
					curUploadedFile.setBankCode("UNKNOWN");
				}

				xlsFileProps.setXlsProtValid(XLSValidator.isXLSProtectionValidByOptions(currFileContent, xlsFileProps.getMatrixLoc(), p));

				xlsFileProps.setUnportectedSheets(XLSValidator.getUnprotectedSheets());

				XLSReader matXlsReader = new XLSReader(new File(xlsFileProps.getMatrixLoc()), p);
				XLSReader masterXlsReader = new XLSReader(currFileContent, p);
				xlsFileProps.setXlsProtPasswValid(XLSValidator.isXLSProtectionPasswordValid(matXlsReader, masterXlsReader));

				xlsFileProps.setNopassSheets(XLSValidator.getNopasswordSheets());

				if (hasUserBank && isXlsFile && xlsFileProps.isXlsProtValid()) {
					xlsFileProps.setValidated(true);
					curUploadedFile.setStatus(new Integer(UploadFileStatus.UPLOADED_VALIDATED.ordinal()).toString());
				} else {
					curUploadedFile.setStatus(new Integer(UploadFileStatus.UPLOADED_NOT_VALIDATED.ordinal()).toString());
					xlsFileProps.setValidated(false);
				}

				curUploadedFile.setNameValid(xlsFileProps.isXlsNameValid());
				curUploadedFile.setExcelVersionValid(isXlsFile);
				Object protInfo = xlsFileProps.getUnportectedSheets();
				curUploadedFile.setProtectionInfo((protInfo != null) ? protInfo.toString() : null);
				Object nopassSheets = xlsFileProps.getNopassSheets();
				curUploadedFile.setPasswordInfo((nopassSheets != null) ? nopassSheets.toString() : null);
				curUploadedFile.setHasUserBank(hasUserBank);
				curUploadedFile.setMatrixValid(true);
				em.merge(curUploadedFile);
				// if (temp.exists())
				// temp.delete();
				if (xlsFileProps.isValidated()) {
					log.info("###################Converting...........");
					convertXlsToXmls(curUploadedFile, xlsFileProps, p, curUserId);
				}

			} catch (MatrixException ex) {
				// if (temp.exists())
				// temp.delete();
				ResourceBundle bundle = null;
				FacesContext fc = FacesContext.getCurrentInstance();
				if (fc != null) {
					UIViewRoot vr = fc.getViewRoot();
					Locale l = vr.getLocale();
					ClassLoader cl = Thread.currentThread().getContextClassLoader();
					bundle = ResourceBundle.getBundle("messages", l, cl);
				} else {
					bundle = MessageBundleReader.loadMessageBundle();
				}
				try {
					// UserTransaction ut = sc.getUserTransaction();
					// ut.begin();

					curUploadedFile.setNameValid(xlsFileProps.isXlsNameValid());
					curUploadedFile.setExcelVersionValid(isXlsFile);
					Object unprotSheets = xlsFileProps.getUnportectedSheets();
					curUploadedFile.setProtectionInfo((unprotSheets != null) ? unprotSheets.toString() : null);
					if ((xlsFileProps.getUnconfirmedSheets() != null) && (xlsFileProps.getUnconfirmedSheets().size() > 0)) {
						curUploadedFile.setProtectionInfo(xlsFileProps.getUnconfirmedSheets().toString());
					}
					Object nopassSheets = xlsFileProps.getNopassSheets();
					curUploadedFile.setPasswordInfo((nopassSheets != null) ? nopassSheets.toString() : null);

					curUploadedFile.setMatrixValid(false);
					curUploadedFile.setReason(ex.getMessage());
					curUploadedFile.setStatus(new Integer(UploadFileStatus.UPLOADED_NOT_VALIDATED.ordinal()).toString());

					bankCode = XlsNameParser.getBankCode(tempName, xlsFileProps.getXlsCorrectPattern());

					List<String> userBanks = (username.equals("sa")) ? sysUserBankLocal.getAllUsersBankCodes(curUserId) : bankLocal.getAllBankCodes();
					hasUserBank = userBanks.contains(bankCode);
					if (hasUserBank) {
						curUploadedFile.setBankCode(bankCode);
					} else {
						curUploadedFile.setBankCode("UNKNOWN");
					}
					curUploadedFile.setHasUserBank(hasUserBank);
					em.merge(curUploadedFile);
					// ut.commit();
				} catch (Exception exx) {
					log.error(exx.getMessage(), exx);
				}
				log.error(bundle.getString(ex.getMessage()), ex);

			} catch (Exception ex) {
				// if (temp != null && temp.exists())
				// temp.delete();
				try {
					// UserTransaction ut = sc.getUserTransaction();
					// ut.begin();
					curUploadedFile.setNameValid(true);
					curUploadedFile.setExcelVersionValid(xlsFileProps.isXlsFile());
					Object protInfo = xlsFileProps.getUnportectedSheets();
					curUploadedFile.setProtectionInfo((protInfo != null) ? protInfo.toString() : null);
					Object noPassSheets = xlsFileProps.getNopassSheets();
					curUploadedFile.setBankCode(XlsNameParser.getBankCode(curUploadedFile.getFileName(), xlsFileProps.getXlsCorrectPattern()));
					curUploadedFile.setPasswordInfo((noPassSheets != null) ? noPassSheets.toString() : null);

					curUploadedFile.setMatrixValid(false);
					curUploadedFile.setStatus(new Integer(UploadFileStatus.UPLOADED_NOT_VALIDATED.ordinal()).toString());

					String fiCode = curUploadedFile.getBankCode();

					List<String> userBanks = sysUserBankLocal.getAllUsersBankCodes(curUserId);
					for (int i = 0; i < userBanks.size(); i++) {
						if (userBanks.get(i).trim().equals(fiCode)) {
							hasUserBank = true;
							break;
						}
					}
					curUploadedFile.setHasUserBank(hasUserBank);

					em.merge(curUploadedFile);
					// ut.commit();
				} catch (Exception exx) {
					log.error(exx.getMessage(), exx);
				}
				log.error(ex.getMessage(), ex);
			}
			return xlsFileProps.isValidated();
		}

	}

	public void convertXlsToXmls(UploadedFile current, XlsFileProperties xlsFileProps, Properties p, BigDecimal userId) throws Exception {
		File temp = null;
		// UploadedFileLocal uploadFileLocal = null;
		try {

			log.info("Converting...." + current.getFileName());

			// temp = new File(current.getFileName());
			// FileOutputStream fos = new FileOutputStream(temp);
			// fos.write(current.getUploadedFile());
			// fos.flush();
			// fos.close();

			XLSReader xlsReader = new XLSReader(new File(xlsFileProps.getMatrixLoc()), current.getUploadedFile(), current.getFileName(), p);
			String sheets[] = xlsReader.getAllSheets();

			JAXBContext jc = JAXBContext.newInstance(RETURN.class);
			Marshaller mar = jc.createMarshaller();

			Language lang = null;

			int langId = 1;
			if (current.getType().ordinal() == 0) {

			} else if (current.getType().ordinal() == 1) {
				// DCS
				// userId = userLocal.getUserId("sa");
				lang = langLocal.findByCode(langLocal.getLangCode());
				langId = lang.getId().intValue();
			} else if (current.getType().ordinal() == 2) {
				// EMAIL
				// userId = userLocal.getUserId("sa");
				lang = langLocal.findByCode("en_US");
				lang.getId().intValue();
			}
			List<MatrixOptionsRowInfo> rowInfo = XLSValidator.getRowInfo();
			String matrixName = xlsReader.getMatrix().getName();
			String version = null;
			for (MatrixOptionsRowInfo info : rowInfo) {
				if (info.getTypeMatrix().equals(matrixName)) {
					version = info.getRetVersion();
					break;
				}
			}
			SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
			for (int i = 0; i < sheets.length; i++) {

				if (sheets[i] == null || sheets[i].toLowerCase().trim().equals("options"))
					continue;
				RETURN cr = new RETURN();

				HEADERTYPE ht = null;

				ht = xlsReader.getHeaderType(version, sheets[i], xlsFileProps.getXlsCorrectPattern());

				BODYTYPE bt = new BODYTYPE();

				List<ITEMTYPE> items = xlsReader.getAllItems(sheets[i]);

				bt.getITEM().addAll(items);

				cr.setHEADER(ht);
				cr.setBODY(bt);

				storeFiles(sdf, userId.intValue(), langId, cr, current, mar);
				log.info("Converted successfully...");

			}

			current.setStatus(new Integer(UploadFileStatus.UPLOADED_CONVERTED.ordinal()).toString());
			em.merge(current);

		} catch (Exception ex) {
			log.error("Could not convert....");
			log.error(ex.getMessage(), ex);
			current.setStatus(new Integer(UploadFileStatus.UPLOADED_NOT_CONVERTED.ordinal()).toString());
			em.merge(current);
			throw ex;

		} finally {
			// if (temp != null && temp.exists())
			// temp.delete();
		}
	}

	public void storeFiles(SimpleDateFormat sdf, int userId, int langId, RETURN ret, UploadedFile current, Marshaller mar) throws JAXBException, IOException, ParseException {

		HEADERTYPE ht = ret.getHEADER();

		mar.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, Boolean.TRUE);
		ArrayList<ImportReturn> impRets = new ArrayList<ImportReturn>();

		try {

			ByteArrayOutputStream bos = new ByteArrayOutputStream();
			//File f = new File("D:\\generated\\"+ht.getRETURNCODE() + "_" + ht.getBANKCODE() +"_.xml");
			mar.marshal(ret, bos);

			// InputStream is = new FileInputStream(f);

			ImportReturn impRet = new ImportReturn();

			impRet.setBankCode(ht.getBANKCODE());

			impRet.setContent(bos.toByteArray());
			// is.close();
			// f.delete();

			// impRet.setId(irLocal.getMaxReturnId());
			impRet.setPeriodEnd(sdf.parse(ht.getPERIODEND()));
			impRet.setPeriodStart(sdf.parse(ht.getPERIODFROM()));
			impRet.setReturnCode(ht.getRETURNCODE());
			impRet.setStatus(ImportReturnStatus.UPLOADED);
			if (current.getType().ordinal() == 1)
				impRet.setType(UploadType.DCS);
			else if (current.getType().ordinal() == 2)
				impRet.setType(UploadType.EMAIL_ROBOT);
			impRet.setUploadTime(new Date());
			impRet.setUserId(userId);
			impRet.setLangId(langId);
			impRet.setVersionCode(ht.getVER());
			impRet.setXlsId(current.getId());

			impRets.add(impRet);
			// irLocal.addReturn(impRet);
			// em.persist(impRet);

			irLocal.addReturns(impRets);

		} catch (Exception ex) {
			log.error(ex.getMessage(), ex);
		}
	}

	public Map<String, List<UploadedFileInfo>> upload(Map<String, byte[]> uploadFiles, User user) {
		Map<String, List<UploadedFileInfo>> fileStats = new HashMap<String, List<UploadedFileInfo>>();
		String name = "";
		try {

			// SysPropertyLocal spl = (SysPropertyLocal)
			// Component.getInstance("propSession");
			String spDefaultPattern = sysPropLocal.findPropValue("fina2.mfb.xls.name.pattern");
			String spMfbHisPattern = sysPropLocal.findPropValue("fina2.mfb.xls.historic.name.pattern");
			String spProtPass = sysPropLocal.findPropValue("fina2.sheet.protection.password");

			Iterator<String> iter = uploadFiles.keySet().iterator();

			// ResourceBundle bundle = MessageBundleReader.loadMessageBundle();
			while (iter.hasNext()) {
				name = iter.next();
				if (name.toLowerCase().contains(".zip")) {
					byte[] zipContent = uploadFiles.get(name);
					FileUtil fu = new FileUtil();
					int status = fu.readFromZip(name, uploadFiles, zipContent);
					if (status == DCSExceptionConstants.DCS_ZIP_NUMBER_FILES_ERROR.ordinal())
						throw new DCSException(DCSExceptionConstants.DCS_ZIP_NUMBER_FILES_ERROR.getMessageCode());
					else if (status == DCSExceptionConstants.DCS_ZIP_XLS_NAME_ERROR.ordinal())
						throw new DCSException(DCSExceptionConstants.DCS_ZIP_XLS_NAME_ERROR.getMessageCode());
					// readFromZip(name, uploadFiles.get(name), uploadFiles);
					uploadFiles.remove(name);
					name = name.replace(".zip", ".xls");
				}
				List<UploadedFileInfo> stat = upload(name, uploadFiles.get(name), user, spDefaultPattern, spMfbHisPattern, spProtPass);

				fileStats.put(name, stat);

			}
		} catch (DCSException ex) {

			name = name.replace(".xls", ".zip");
			List<UploadedFileInfo> fileInfo = new ArrayList<UploadedFileInfo>();
			UploadedFileInfo info = new UploadedFileInfo();
			info.setStatus(UploadFileStatus.UPLOADED_NOT_VALIDATED.ordinal());
			List<String> messageCodes = new ArrayList<String>();
			messageCodes.add(ex.getMessage());
			info.setMessageCodes(messageCodes);
			fileInfo.add(info);
			fileStats.put(name, fileInfo);

		} catch (Exception ex) {
			log.error(ex.getMessage(), ex);
		}
		return fileStats;
	}

	public String upload(Map<String, byte[]> uploadFiles, User user, Properties p) {
		try {

			String spDefaultPattern = sysPropLocal.findPropValue("fina2.mfb.xls.name.pattern");
			String spMfbHisPattern = sysPropLocal.findPropValue("fina2.mfb.xls.historic.name.pattern");
			String spProtPass = sysPropLocal.findPropValue("fina2.sheet.protection.password");

			Iterator<String> iter = uploadFiles.keySet().iterator();
			while (iter.hasNext()) {
				String name = iter.next();
				log.info("##################Current Name Of Xls : " + name);
				upload(name, uploadFiles.get(name), user, spDefaultPattern, spMfbHisPattern, spProtPass, p);
			}
		} catch (Exception ex) {
			log.error(ex.getMessage(), ex);
		}
		return "uploaded";
	}

	public UploadedFile findByPrimaryKey(long id) {
		UploadedFile uf = em.find(UploadedFile.class, id);
		return uf;
	}

	public long getMaxId() {
		Long max = 0l;
		try {
			UserTransaction ut = sc.getUserTransaction();
			ut.begin();
			max = (Long) em.createQuery("SELECT MAX(id) FROM " + UploadedFile.class.getName() + " u").getSingleResult();
			if (max == null) {
				max = 0l;
			}
			ut.commit();
		} catch (Exception ex) {
			log.error(ex.getMessage(), ex);
		}
		return max;
	}

	public int getMaxReturnId() {
		Integer max = 0;
		try {
			UserTransaction ut = sc.getUserTransaction();
			ut.begin();
			max = (Integer) em.createQuery("SELECT MAX(u.id) FROM " + ImportReturn.class.getName() + " u").getSingleResult();
			if (max == null) {
				max = 0;
			}
			ut.commit();
		} catch (Exception ex) {
			log.error(ex.getMessage(), ex);
		}
		return max;
	}

	@Interceptors(UploadedFileInterceptor.class)
	public long getDataAmmountByStatus(String username, Integer status) {
		Long count = 0l;
		try {
			Query q = em.createQuery("SELECT COUNT(u) FROM " + UploadedFile.class.getName() + " u WHERE u.status=:status AND u.username=:username");
			q.setParameter("status", status.toString());
			q.setParameter("username", username);
			count = ((Long) q.getSingleResult());

		} catch (NoResultException ex) {
			count = 0l;
		} catch (Exception ex) {
			log.error(ex.getMessage(), ex);
		}

		return count;
	}

	@PostConstruct
	public void postConstruct() {
		log.info("Post Construct");
	}

	@PrePassivate
	public void prePassivate() {
		// log.info("Pre Passivating");
	}

	@PostActivate
	public void postActivate() {
		// log.info("Post Activation");
	}

	@PreDestroy
	public void preRemove() {
		// log.info("Pre Removing ");
	}

	@Remove
	public void remove() {

	}

	public List<ImportReturn> loadXmls() {
		try {
			// log.info("Loading Xmls...");
			xmls.clear();
			Query q = em.createQuery("SELECT u.returnCode,u.bankCode,u.periodStart,u.periodEnd,u.importStart,u.importEnd,u.status,u.versionCode,u.type,u.message FROM " + ImportReturn.class.getName() + " u WHERE u.xlsId=:xlsId ORDER BY u.returnCode ASC");
			q.setParameter("xlsId", fileId);
			List<Object[]> xmlsData = q.getResultList();
			if (xmlsData != null) {
				for (int i = 0; i < xmlsData.size(); i++) {
					ImportReturn ir = new ImportReturn();

					bankCode = xmlsData.get(i)[1].toString();
					periodStart = (Date) xmlsData.get(i)[2];
					periodEnd = (Date) xmlsData.get(i)[3];

					ir.setReturnCode(xmlsData.get(i)[0].toString());
					UploadType type = (UploadType) xmlsData.get(i)[8];
					if (type != null)
						ir.setType(type);
					ir.setVersionCode(xmlsData.get(i)[7].toString());
					ir.setImportStart((Date) xmlsData.get(i)[4]);
					ir.setImportEnd((Date) xmlsData.get(i)[5]);
					int status = Integer.parseInt(xmlsData.get(i)[6].toString());
					ir.setStatus(status);
					Object msg = xmlsData.get(i)[9];
					ir.setMessage((msg != null) ? msg.toString() : "");
					xmls.add(ir);
				}
			}
			// log.info("XMLS Loaded");
		} catch (Exception ex) {
			log.error(ex.getMessage(), ex);
		}
		return xmls;
	}

	public long getFileId() {
		return fileId;
	}

	public void setFileId(long fileId) {
		this.fileId = fileId;
	}

	public List<ImportReturn> getXmls() {
		xmls = loadXmls();
		return xmls;
	}

	public void setXmls(List<ImportReturn> xmls) {
		this.xmls = xmls;
	}

	public String getHeader() {
		return "[" + bankCode + "]" + " " + periodStart + " - " + periodEnd;
	}

	public void clear() {
		uploadedFiles.clear();
	}

}
